package com.rlyy.rsj.dao;

import com.rlyy.aibase.utils.base.MapNode;
import com.rlyy.aibase.utils.base.TableNode;
import org.apache.poi.ss.formula.functions.T;
import rsj.annotation.Param;
import rsj.annotation.Select;

/**
 * @author yuanqinglong
 * @since 2021/6/2 17:49
 */
public interface OrgpDao2 {

    @Select(" SELECT orgp_id,orgp_org_id as org_id,orgp_prod_uid,orgp_prod_name,orgp_buyamount,orgp_useamount " +
            " FROM sys.sys_orgp " +
            " WHERE orgp_type in ('1','2') and orgp_orgpstatus = '1' and orgp_status in ('10', '20', '55') ")
    TableNode getALLOrgp();

    /**
     * 临时为中信统计流水表
     * @param prodUid
     * @return
     */
    @Select(" with all_restatus as ( " +
            " select log_user_id, log_orgp_id, max(orgp.orgp_prod_uid) as orgp_prod_uid, " +
            " max(orgp.orgp_prod_name) as orgp_prod_name, log_prod_uid, max(orgp.orgp_buyamount) as orgp_buyamount, " +
            " count(1) as usecount, log_restatus" +
            " FROM sys.oper_log log LEFT JOIN sys.sys_orgp orgp ON log.log_orgp_id = orgp.orgp_id" +
            " WHERE orgp.orgp_type = '1' AND orgp.orgp_orgpstatus = '1' AND orgp.orgp_endtime > now() AND orgp.orgp_status in ('10', '20', '55') AND " +
            " log_prod_uid = #{prodUid} " +
            " GROUP BY log_user_id,log_orgp_id, log_prod_uid,log_restatus " +
            " ) select log_orgp_id as orgp_id, max(auth.aut_org_id) as org_id, max(orgp_prod_uid) as orgp_prod_uid," +
            " max(orgp_prod_name) as orgp_prod_name, max(orgp_buyamount) as orgp_buyamount, sum(usecount)::INTEGER as orgp_useamount" +
            " FROM all_restatus  LEFT JOIN sys.sys_authon auth ON all_restatus.log_user_id = auth.aut_keyid " +
            " WHERE log_restatus IN ('RSJ000','RSJ014')" +
            " GROUP BY log_orgp_id")
    TableNode getOrgpByProdUid(@Param("prodUid") String prodUid);

    @Select(" SELECT p.orgp_id,p.orgp_prod_uid,p.orgp_prod_name,p.orgp_buyamount,p.orgp_useamount,p.orgp_endtime,p.orgp_bus_id,c.cust_name,c.cust_sname,a.aut_keyid FROM sys.sys_orgp p " +
            " LEFT JOIN sys.sys_authon a on p.orgp_org_id = a.aut_org_id " +
            " LEFT JOIN sys.sys_org o on p.orgp_org_id = o.org_id " +
            " LEFT JOIN sys.sys_cust c on o.org_cust_id = c.cust_id " +
            " WHERE orgp_orgpstatus = '1' and orgp_type in ('1','2') " +
            " and orgp_status in ('10','20','55') ")
    TableNode getOrgpRemindInfo();

}
